<?php
/**
 * Created by PhpStorm.
 * User: JSQ-SL
 * Date: 2018/11/19
 * Time: 16:55
 */

namespace app\index\controller;

use app\index\base_controller\BaseController;
use app\index\service\ExcelService;
use app\index\table\BaseTable;
use app\index\table\FacilityTable;

use app\index\table\FurnTable;
use app\index\table\IntangibleTable;
use app\index\table\InvestTable;
use app\index\table\LandTable;
use app\index\table\LiquidTable;
use app\index\table\RateInTable;
use app\index\table\RateOutTable;
use app\index\table\StockTable;
use think\Controller;
use think\Db;
use think\Exception;

use think\facade\Env;

class Test extends Controller
{
    public function testApi(){
        dump('111');

        $xml = <<<EOF
<ROOT>
    <CONTANST>
        <ASSETSCHEME>CARD_ADD</ASSETSCHEME>
        <USERNAME>105000</USERNAME>
        <UNITCODE>105000</UNITCODE>
        <PASSWORD>105000</PASSWORD>
    </CONTANST>
    <ASSETDATA>
        <DATA>
            <BILLCODE>CS001</BILLCODE>
            <ZICFLID>2030201</ZICFLID>
            <ORGUNIT>0901</ORGUNIT>
            <JIZRQ>2014-10-24</JIZRQ>
            <JIZA>1000</JIZA>
        </DATA>
        <DATA>
            <BILLCODE>CS002</BILLCODE>
            <ZICFLID>2030202</ZICFLID>
            <ORGUNIT>0901</ORGUNIT>
            <JIZRQ>2014-10-24</JIZRQ>
            <JIZA>1000</JIZA>
        </DATA>
    </ASSETDATA>
</ROOT>
EOF;
        dump($xml);

        $curl = curl_init();

        curl_setopt_array($curl, array(
             CURLOPT_PORT => "8023",
            CURLOPT_URL=>"http://221.122.73.177:8023/dna_ws/IDService?wsdl/writeToAsset",
            CURLOPT_RETURNTRANSFER => true,
            CURLOPT_ENCODING => "",
            CURLOPT_MAXREDIRS => 10,
            CURLOPT_TIMEOUT => 10,
            CURLOPT_HTTP_VERSION => CURL_HTTP_VERSION_1_1,
            CURLOPT_CUSTOMREQUEST => "POST",
            CURLOPT_POSTFIELDS => $xml,
        ));
//        echo "header:".json_encode($header)."<br>";
//        echo "param:".$param."<br>";
        $response = curl_exec($curl);
        $err = curl_error($curl);
        curl_close($curl);

        dump($response);
        dump($err);

    }


    public function testFurn(){

//        $data = Db::table('APP_MANAGER.FURN_INFO')->find();
//        dump($data);

        $sql = "(SELECT 
         F.FURN_NO
         ,F.FURN_NAME
         ,TO_CHAR(F.ENTER_TIME,'yyyy-mm-dd') ENTER_TIME
         ,F.FURNTYPE_NO
         ,F.DEPART_ID
         ,cast(DP.DEPART_SHORT as varchar2(30)) DEPART_SHORT
         ,DC.COLLEGE_ID
         ,cast(DC.COLLEGE as varchar2(90)) COLLEGE
         ,F.SPEC
         ,F.MODEL
         ,F.PRICE
         ,F.FACTORY
         ,F.COUNTRY_ID
         ,F.FACTORY_NO
         ,TO_CHAR(F.FACTORY_DATE,'yyyy-mm-dd') FACTORY_DATE
         ,TO_CHAR(F.GET_DATE,'yyyy-mm-dd') GET_DATE
         ,F.STATE_ID
         ,F.GET_PERSON
         ,F.CHECK_PERSON
         ,F.CHARGE_PERSON
         ,F.REMARK
         ,F.GB
         ,F.ZJLY
         ,F.ZJLY_DETAIL
         ,F.BELONG_ID
         ,F.ASSET_TYPE_ID
         ,F.BRAND
         ,F.PLACE
         ,F.REMARK2
         ,F.ID
         ,F.NOTICE_NO
         ,F.MUTI_BUY_MARK
         ,F.SUBMIT_MARK
         ,F.USEDIRECTION_ID
         ,F.MIN_SERVICE_LIFE
         ,F.SERVICE_LIFE
         ,F.UNUSED_LIFE
         ,F.DEPRECIATION_THIS_MONTH
         ,F.ACCUMULATIVE_DEPRECIATION
         ,F.CURRENT_VALUE
         ,TO_CHAR(F.LAST_DEPRECIATION_DATE,'yyyy-mm-dd') LAST_DEPRECIATION_DATE
         ,F.FINANCE_DEPARTMENT
         ,F.FINANCE_INPUT_DATE
         FROM APP_MANAGER.FURN_INFO F
         LEFT JOIN APP_MANAGER.DICT_SB_DEPARTMENT DP ON F.DEPART_ID=DP.DEPART_ID
         LEFT JOIN APP_MANAGER.DICT_COLLEGE DC ON DC.COLLEGE_ID=DP.DEPART_BELONG
         )";

       $data = Db::table($sql)
           ->where([['GET_DATE','<=','2018-12-31']])
           ->limit(0,10)
           ->order('GET_DATE DESC')
           ->select();
       $total = Db::table($sql)->where([['GET_DATE','<=','2018-12-31']])->count();
       dump($data);
       dump($total);

       $fieldList = [
           ['title'=>"FURN_NO家具编号",'field'=>'FURN_NO']
           ,['title'=>'FURN_NAME家具名称','field'=>'FURN_NAME']
           ,['title'=>'ENTER_TIME入账时间','field'=>'ENTER_TIME']
           ,['title'=>'FURNTYPE_NO','field'=>'FURNTYPE_NO']
           ,['title'=>'DEPART_ID使用部门','field'=>'DEPART_ID']
           ,['title'=>'使用单位','field'=>'COLLEGE']
           ,['title'=>'SPEC技术指标','field'=>'SPEC']
           ,['title'=>'MODEL型号','field'=>'MODEL']
           ,['title'=>'PRICE单价','field'=>'PRICE']
           ,['title'=>'FACTORY厂家','field'=>'FACTORY']
           ,['title'=>'COUNTRY_ID国别','field'=>'COUNTRY_ID']
           ,['title'=>'FACTORY_NO出厂编号','field'=>'FACTORY_NO']
           ,['title'=>'FACTORY_DATE出厂日期','field'=>'FACTORY_DATE']
           ,['title'=>'GET_DATE购置日期','field'=>'GET_DATE']
           ,['title'=>'STATE_ID状态','field'=>'STATE_ID']
           ,['title'=>'GET_PERSON领用人','field'=>'GET_PERSON']
           ,['title'=>'CHECK_PERSON经手人','field'=>'CHECK_PERSON']
           ,['title'=>'CHARGE_PERSON负责人','field'=>'CHARGE_PERSON']
           ,['title'=>'REMARK备注','field'=>'REMARK']
           ,['title'=>'GB国标','field'=>'GB']
           ,['title'=>'ZJLY资金来源','field'=>'ZJLY']
           ,['title'=>'ZJLY_DETAIL来源明细','field'=>'ZJLY_DETAIL']
           ,['title'=>'BELONG_ID隶属单位','field'=>'BELONG_ID']
           ,['title'=>'ASSET_TYPE_ID资产分类号','field'=>'ASSET_TYPE_ID']
           ,['title'=>'BRAND品牌','field'=>'BRAND']
           ,['title'=>'PLACE存放地点','field'=>'PLACE']
           ,['title'=>'REMARK2备注2','field'=>'REMARK2']
           ,['title'=>'ID临时编号','field'=>'ID']
           ,['title'=>'NOTICE_NO发票号','field'=>'NOTICE_NO']
           ,['title'=>'MUTI_BUY_MARK是否批量购买','field'=>'MUTI_BUY_MARK']
           ,['title'=>'SUBMIT_MARK是否提交','field'=>'SUBMIT_MARK']
           ,['title'=>'USEDIRECTION_ID使用方向','field'=>'USEDIRECTION_ID']
           ,['title'=>'MIN_SERVICE_LIFE','field'=>'MIN_SERVICE_LIFE']
           ,['title'=>'SERVICE_LIFE','field'=>'SERVICE_LIFE']
           ,['title'=>'UNUSED_LIFE','field'=>'UNUSED_LIFE']
           ,['title'=>'DEPRECIATION_THIS_MONTH','field'=>'DEPRECIATION_THIS_MONTH']
           ,['title'=>'ACCUMULATIVE_DEPRECIATION','field'=>'ACCUMULATIVE_DEPRECIATION']
           ,['title'=>'CURRENT_VALUE','field'=>'CURRENT_VALUE']
           ,['title'=>'LAST_DEPRECIATION_DATE','field'=>'LAST_DEPRECIATION_DATE']
           ,['title'=>'FINANCE_DEPARTMENT','field'=>'FINANCE_DEPARTMENT']
           ,['title'=>'FINANCE_INPUT_DATE','field'=>'FINANCE_INPUT_DATE']
       ];

       $excel = new ExcelService();
       $excel->exportExcel($data,'bbb',$fieldList);
    }


    public function testsql(){

        $sql2 = "(SELECT
TJUGZ.ASSET_BOOK.BOOK_NO,
TJUGZ.ASSET_BOOK.ASSET_TYPE_ID,
TJUGZ.ASSET_DICT_TYPE94TO2010.CNAME94 AS ASSET_TYPE,
TJUGZ.ASSET_BOOK.BOOK_NAME,
TJUGZ.ASSET_BOOK.OLD_BOOK_NO,
TJUGZ.ASSET_BOOK.SAVE_ROOM,
TJUGZ.ASSET_BOOK.PURCHASE_ID,
TJUGZ.ASSET_DICT_PURCHASE.PURCHASE,
TJUGZ.ASSET_BOOK.BUDGET_NO,
TJUGZ.ASSET_BOOK.GETTYPE_ID,
TJUGZ.ASSET_DICT_GETTYPE.GETTYPE,
TO_CHAR(TJUGZ.ASSET_BOOK.GET_DATE,'yyyy-mm-dd') AS GET_DATE,
TJUGZ.ASSET_BOOK.USE_LIFE,
TJUGZ.ASSET_BOOK.SELLER,
TO_CHAR(TJUGZ.ASSET_BOOK.REPAIR_DEADLINE,'yyyy-mm-dd') AS REPAIR_DEADLINE,
TJUGZ.ASSET_BOOK.BOOK_NUM,
TJUGZ.ASSET_BOOK.ENTER_TYPE_ID,
TJUGZ.ASSET_DICT_ENTER_TYPE.ENTER_TYPE,
TJUGZ.ASSET_BOOK.VALUETYPE_ID,
TJUGZ.ASSET_DICT_VALUETYPE.VALUETYPE,
TJUGZ.ASSET_BOOK.BOOK_FEE,
TJUGZ.ASSET_BOOK.FINANCE_GET_FEE,
TJUGZ.ASSET_BOOK.NON_FINANCE_FEE,
TJUGZ.ASSET_BOOK.AVG_FEE,
TJUGZ.ASSET_BOOK.ACCOUNT_CERT_NO,
TO_CHAR(TJUGZ.ASSET_BOOK.ENTER_DATE,'yyyy-mm-dd') AS ENTER_DATE,
TJUGZ.ASSET_BOOK.STATUS_ID,
TJUGZ.ASSET_DICT_STATUS.STATUS,
TO_CHAR(TJUGZ.ASSET_BOOK.START_USE_DATE,'yyyy-mm-dd') AS START_USE_DATE,
TJUGZ.ASSET_BOOK.SAVE_YEAR,
TJUGZ.ASSET_BOOK.USEDIRECTION_ID,
TJUGZ.ASSET_DICT_USEDIRECTION.USEDIRECTION,
TJUGZ.ASSET_BOOK.COLLEGE_ID,
C1.SHORTNAME as USE_COLLEGE,
TJUGZ.ASSET_BOOK.MGR_COLLEGE_ID,
C2.SHORTNAME as MGR_COLLEGE,
TJUGZ.ASSET_BOOK.USE_NAME,
TJUGZ.ASSET_BOOK.FILE_NO,
TJUGZ.ASSET_BOOK.PRESS_NAME,
TO_CHAR(TJUGZ.ASSET_BOOK.PRESS_DATE,'yyyy-mm-dd') AS PRESS_DATE,
TJUGZ.ASSET_BOOK.DEPRECIATE_STATUS_ID,
TJUGZ.ASSET_DICT_DEPRECIATE.DEPRECIATE,
TJUGZ.ASSET_BOOK.DEPRECIATE_WAY,
TJUGZ.ASSET_BOOK.DEPRECIATE_LIFE,
TJUGZ.ASSET_BOOK.REDUCE_PRE,
TJUGZ.ASSET_BOOK.DEPRECIATE_PER_MON,
TJUGZ.ASSET_BOOK.DEPRECIATE_PERCENTAGE,
TJUGZ.ASSET_BOOK.DEPRECIATED_MONTH,
TJUGZ.ASSET_BOOK.ACC_DEPRECIATE,
TJUGZ.ASSET_BOOK.REMARK,
TJUGZ.ASSET_BOOK.INPUT_USER_ID,
TJUGZ.EMPLOYEE.NAME AS INPUT_NAME,
TO_CHAR(TJUGZ.ASSET_BOOK.INPUT_TIME,'yyyy-mm-dd') AS INPUT_TIME,
TJUGZ.ASSET_BOOK.BELONG_ID,
TJUGZ.ASSET_DICT_BELONG.BELONG

FROM
TJUGZ.ASSET_BOOK
LEFT JOIN TJUGZ.ASSET_DICT_TYPE94TO2010 ON TJUGZ.ASSET_DICT_TYPE94TO2010.CCODE94 = TJUGZ.ASSET_BOOK.ASSET_TYPE_ID
LEFT JOIN TJUGZ.ASSET_DICT_PURCHASE ON TJUGZ.ASSET_DICT_PURCHASE.PURCHASE_ID = TJUGZ.ASSET_BOOK.PURCHASE_ID
LEFT JOIN TJUGZ.ASSET_DICT_GETTYPE ON TJUGZ.ASSET_DICT_GETTYPE.GETTYPE_ID = TJUGZ.ASSET_BOOK.GETTYPE_ID
LEFT JOIN TJUGZ.ASSET_DICT_ENTER_TYPE ON TJUGZ.ASSET_DICT_ENTER_TYPE.ENTER_TYPE_ID = TJUGZ.ASSET_BOOK.ENTER_TYPE_ID
LEFT JOIN TJUGZ.ASSET_DICT_VALUETYPE ON TJUGZ.ASSET_DICT_VALUETYPE.VALUETYPE_ID = TJUGZ.ASSET_BOOK.VALUETYPE_ID
LEFT JOIN TJUGZ.ASSET_DICT_STATUS ON TJUGZ.ASSET_DICT_STATUS.STATUS_ID = TJUGZ.ASSET_BOOK.STATUS_ID
LEFT JOIN TJUGZ.ASSET_DICT_USEDIRECTION ON TJUGZ.ASSET_DICT_USEDIRECTION.USEDIRECTION_ID = TJUGZ.ASSET_BOOK.USEDIRECTION_ID
LEFT JOIN TJUGZ.DICT_COLLEGE C1 ON C1.COLLEGE_ID = TJUGZ.ASSET_BOOK.COLLEGE_ID
LEFT JOIN TJUGZ.DICT_COLLEGE C2 ON C2.COLLEGE_ID = TJUGZ.ASSET_BOOK.MGR_COLLEGE_ID
LEFT JOIN TJUGZ.ASSET_DICT_DEPRECIATE ON TJUGZ.ASSET_DICT_DEPRECIATE.DEPRECIATE_ID = TJUGZ.ASSET_BOOK.DEPRECIATE_STATUS_ID
LEFT JOIN TJUGZ.EMPLOYEE ON TJUGZ.EMPLOYEE.STAFF_ID = TJUGZ.ASSET_BOOK.INPUT_USER_ID
LEFT JOIN TJUGZ.ASSET_DICT_BELONG ON TJUGZ.ASSET_DICT_BELONG.BELONG_ID = TJUGZ.ASSET_BOOK.BELONG_ID)";

        $data = Db::table($sql2)
                ->where('asset_type','exp','is not null')
            ->select();
        dump($data);
    }

    //导出设备信息
    public function testFacility(){
        set_time_limit(800);
        ini_set('memory_limit','1024M');

        $start = 0;
        $len = 100;
        $end = $start + $len;

        $fieldList = ["'4112010056' SCHOOL_ID"," cast('天津大学' as varchar2(20)) SCHOOL_NAME","fa.FACILITY_NO"
            ,"d9.REJECT_ID","fa.INDENT_ID","cast(fa.FACILITY as varchar2(90)) FACILITY"
            ,"d.FACITYPE_ID","cast(d2.facitype as varchar2(30)) facitype","fa.GUOZI_SORT_DEPRECIATION","cast(d10.TYPE_NAME as varchar2(150)) GUOZI_NAME"
            ,"'' LAND11","to_char(d.AUDIT_DATE,'yyyymmdd') GET_DATE","'' LAND13","'' LAND14","'' LAND15","'' LAND16","'' LAND17"
            ,"'' LAND18","'' LAND19","cast(fa.MODEL as varchar2(90)) MODEL","cast(fa.TECH_SPEC as varchar2(300)) TECH_SPEC"
            ,"cast(fa.FACTORY as varchar2(60)) factory","d3.COUNTRY_CNNAME","cast(d.AGENT as varchar2(90)) AGENT"
            ,"cast('台' as varchar2(3)) UNIT","cast('原值' as varchar2(6)) VALUE_TYPE","fa.PRICE","fa.MIN_SERVICE_LIFE","'1' as COUNT"
            ,"fa.RECEIPT_NO","cast('分散采购' as varchar2(12)) BUYTYPE","cast('新购' as varchar2(6)) GETTYPE","to_char(d.AUDIT_DATE,'yyyymmdd') AUDIT_DATE"
            ,"to_char(fa.entertime,'yyyymmdd') ENTERTIME","d4.STATE",'fa.CLASSIFICATION','fa.usedirection_id','cast(d5.usedirection as varchar2(12)) USEDIRECTION'
            ,"cast(d7.college as varchar2(60)) COLLEGE",'cast(d.depart_name as varchar2(30)) PLACE_STORE','cast(d8.finin as varchar2(30)) FININ',"cast(fa.person_get as varchar2(60)) PERSON_GET","cast(fa.comments as varchar2(300)) COMMENTS"];
        $data = Db::table('app_manager.sb_facility')
            ->alias('fa')
            ->field($fieldList)
            ->where("to_char(fa.entertime,'yyyy-mm-dd') >='2015-01-01' and to_char(fa.entertime,'yyyy-mm-dd')<='2018-09-30'")
            ->limit($start,$len)
            ->order('fa.entertime asc')
            ->leftJoin("app_manager.sb_receive_main d","d.receive_id=fa.receive_id")
            ->leftJoin("app_manager.dict_sb_facitype d2",'d2.facitype_id=d.facitype_id')
            ->leftJoin("app_manager.dict_country d3","d3.COUNTRY_ID=fa.country_id")
            ->leftJoin("app_manager.dict_sb_state d4",'d4.state_id=fa.state_id')
            ->leftJoin('app_manager.dict_sb_usedirection d5','d5.usedirection_id=fa.usedirection_id')
            ->leftJoin('app_manager.dict_sb_department d6','d6.depart_id=fa.depart_belong')
            ->leftJoin('app_manager.dict_college d7','d7.college_id=d6.depart_belong')
            ->leftJoin("app_manager.dict_sb_finin d8",'d8.finin_id=fa.finin_id')
            ->leftJoin("app_manager.sb_reject_main d9","d9.facility_no=fa.facility_no and d9.status_id='40'")
            ->leftJoin("app_manager.asset_dict_type2010 d10",'d10.TYPE_ID=fa.GUOZI_SORT_DEPRECIATION')
            ->select();
        dump($data);

        $exportFields = [
            ['title'=>'学校标识码','field'=>'SCHOOL_ID'],
            ['title'=>'学校（机构）名称','field'=>'SCHOOL_NAME'],
            ['title'=>'资产编号','field'=>'FACILITY_NO'],
            ['title'=>'报废编号','field'=>'REJECT_ID'],
            ['title'=>'合同（申购）号','field'=>'INDENT_ID'],
            ['title'=>'资产名称','field'=>'FACILITY'],
            ['title'=>'教育部分类代码','field'=>'FACITYPE_ID'],
            ['title'=>'教育部分类名称','field'=>'FACITYPE'],
            ['title'=>'国标分类代码','field'=>'GUOZI_SORT_DEPRECIATION'],
            ['title'=>'国标分类名称','field'=>'GUOZI_NAME'],
            ['title'=>'土地使用权面积','field'=>'LAND11'],
            ['title'=>'取得日期','field'=>'GET_DATE'],
            ['title'=>'土地使用权类型','field'=>'LAND13'],
            ['title'=>'权属性质','field'=>'LAND14'],
            ['title'=>'地类（用途）','field'=>'LAND15'],
            ['title'=>'投入使用日期','field'=>'LAND16'],
            ['title'=>'建筑面积','field'=>'LAND17'],
            ['title'=>'占地面积','field'=>'LAND18'],
            ['title'=>'坐落位置','field'=>'LAND19'],
            ['title'=>'品牌型号','field'=>'MODEL'],
            ['title'=>'规格型号','field'=>'TECH_SPEC'],
            ['title'=>'生产厂商','field'=>'FACTORY'],
            ['title'=>'生产国','field'=>'COUNTRY_CNNAME'],
            ['title'=>'供货商','field'=>'AGENT'],
            ['title'=>'计量单位','field'=>'UNIT'],
            ['title'=>'价值类型','field'=>'VALUE_TYPE'],
            ['title'=>'价值','field'=>'PRICE'],
            ['title'=>'预计使用年限','field'=>'MIN_SERVICE_LIFE'],
            ['title'=>'数量','field'=>'COUNT'],
            ['title'=>'发票号','field'=>'RECEIPT_NO'],
            ['title'=>'采购组织形式','field'=>'BUYTYPE'],
            ['title'=>'资产来源','field'=>'GETTYPE'],
            ['title'=>'验收通过日期','field'=>'AUDIT_DATE'],
            ['title'=>'财务入账日期','field'=>'ENTERTIME'],
            ['title'=>'使用状况','field'=>'STATE'],
            ['title'=>'用途','field'=>'CLASSIFICATION'],
            ['title'=>'使用方向','field'=>'USEDIRECTION'],
            ['title'=>'使用/管理部门','field'=>'COLLEGE'],
            ['title'=>'存放地点','field'=>'PLACE_STORE'],
            ['title'=>'经费来源','field'=>'FININ'],
            ['title'=>'经费负责人','field'=>'PERSON_GET'],
            ['title'=>'备注','field'=>'COMMENTS'],
        ];
        $excel = new ExcelService();
        $start += 1 ;
        $excel->exportExcel($data,"设备$start-$end",$exportFields);

        $data = Db::table('app_manager.sb_facility')
            ->alias('fa')
            ->where("to_char(entertime,'yyyy-mm-dd') >='2015-01-01' and to_char(entertime,'yyyy-mm-dd')<='2018-09-30'")
            ->count();
        dump($data);
    }

    public function depreciate(){

        $list = FacilityTable::table()->select();
        foreach ($list as $data){

            $dd = $this->getMonthNum(date('Y-m-d'),$data['ENTERTIME']) + 1;
            FacilityTable::update(['FACILITY_NO'=>$data['FACILITY_NO']],
                [
                    'DEPRECIATE_STATUS_ID'=>'02'
                    ,'DEPRECIATE_WAY'=>'平均年限法'
                    ,'DEPRECIATED_MONTH'=>$dd
                    ,'DEPRECIATE_LIFE'=>'72'
                    ,'DEPRECIATE_PER_MON'=>$data['PRICE']*1/72
                    ,'ACC_DEPRECIATE'=>$data['PRICE']*$dd/72
                ]);

        }


    }

    protected  function getMonthNum( $date1, $date2, $tags='-' ){
        $date1 = explode($tags,$date1);
        $date2 = explode($tags,$date2);
        return abs($date1[0] - $date2[0]) * 12 - $date2[1] + abs($date1[1]);
    }

    public function getFieldComment() {

        $table = 'ASSET_EQUIP_FACILITY2';
        $sql = "select * from user_col_comments where table_name='$table'";
        $data = Db::query($sql);

        if(empty($data) || !is_array($data)){
            die("data is not array or data is empty");
        }
        $excel = new ExcelService();
        $excel->__construct();
        /* 实例化类 */
        $objPHPExcel = new \PHPExcel();

        /* 设置输出的excel文件为2007兼容格式 */
        //$objWriter=new PHPExcel_Writer_Excel5($objPHPExcel);//非2007格式
        $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);

        /* 设置当前的sheet */
        $objPHPExcel->setActiveSheetIndex(0);

        $objActSheet = $objPHPExcel->getActiveSheet();

        $colList = ['A','B'];


        /*设置宽度*/
        foreach ($colList as $key=>$value) {
            $objPHPExcel->getActiveSheet()->getColumnDimension($value)->setWidth(15);
        }

        foreach($data as $key => $value){
            /* excel文件内容 */
            $num = $key+1;
            // $value2=iconv("gbk","utf-8",$value2);
            $objActSheet->setCellValue('A'.$num,$value['COLUMN_NAME']);
            $objActSheet->setCellValue('B'.$num,$value['COMMENTS']);
//            foreach ($fieldList as $key=>$field) {
//                $objActSheet->setCellValue($colList[$key].$num,$value[$field['field']]);
//            }
        }

        /* 生成到浏览器，提供下载 */
        ob_end_clean();  //清空缓存
        header("Pragma: public");
        header("Expires: 0");
        header("Cache-Control:must-revalidate,post-check=0,pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename="22.xlsx"');
        header("Content-Transfer-Encoding:binary");

        $objWriter->save('php://output');

//        foreach ($data as $record) {
////            echo "['title'=>'".$record['COMMENTS']."','field'=>'".$record['COLUMN_NAME']."'],<br />";
//            echo $record['COLUMN_NAME'].'=>'.$record['COMMENTS']."<br />";
//
//        }
    }

    public function insetConfig() {
        $id = '1';
        $tableConfig = 'ASSET_STOCK_CONFIG';
        $table = 'ASSET_STOCK';

        $sql = "select * from user_col_comments where table_name='$table'";
        $data = Db::query($sql);


        Db::startTrans();
        try {
            foreach ($data as $record) {
        //            echo "['title'=>'".$record['COMMENTS']."','field'=>'".$record['COLUMN_NAME']."'],<br />";
                $insertData['ID'] = $id;
                $insertData['NAME'] = $record['COLUMN_NAME'];
                $insertData['TITLE'] = $record['COMMENTS'];
                $insertData['TYPE'] = 'text';
                $insertData['FIELD'] = $record['COLUMN_NAME'];
                $insertData['EDIT_FLAG'] = '1';
                $insertData['ADD_FLAG'] = '1';
                $insertData['PRINT_FLAG'] = '1';
                $insertData['IS_ONE_LINE'] = '0';
                Db::table($tableConfig)->insert($insertData);
                $id += 4;
            }
        } catch (Exception $e) {
            Db::rollback();
            echo $e->getMessage();
            return;
        }
        Db::commit();
        echo '0k';
    }


    public function getField() {
        $table = 'ASSET_BOOK';
        $sql = "select * from user_col_comments where table_name='$table'";
        $data = Db::query($sql);

        foreach ($data as $record) {
            echo "['title'=>'".$record['COMMENTS']."','field'=>'".$record['COLUMN_NAME']."'],<br />";

        }
    }

    public function field() {

        $fieldList = StockTable::STOCK_FIELD_LIST;
        $value = '';
        foreach ($fieldList as $field) {
            $value .= '"'.$field['field'].'",';
        }
        echo $value;
    }


    public function test1(){
        set_time_limit(0);
        ini_set('memory_limit','10240M');
        echo date('Y-m-d H:i:s')."<br>";

        $data = Db::table('app_manager.sb_indent_manage')
            ->field("INDENT_ID,ID,FEECARD_ID,INDENT_DATE,PERSON_APPLY,cast(PHONE as varchar2(60)) PHONE
            ,cast(FACILITY as varchar2(90)) FACILITY,cast(SPEC as varchar2(900)) SPEC,FACILITYKIND_ID
            ,cast(PLAN_COMPANY as varchar2(90)) PLAN_COMPANY")
            ->where("rownum < 20000")->select();

        $count = Db::table('app_manager.sb_indent_manage')->where("rownum < 2000")->count();

        dump( $count);

        $fieldList = [
            ['title'=>'流水号','field'=>'INDENT_ID'],
            ['title'=>'资产分类代码','field'=>'ID'],
            ['title'=>'资产名称','field'=>'FEECARD_ID'],
            ['title'=>'原资产编号','field'=>'INDENT_DATE'],
            ['title'=>'存放地点','field'=>'PERSON_APPLY'],
            ['title'=>'采购组织形式','field'=>'PHONE'],
            ['title'=>'预算项目编号','field'=>'FACILITY'],
            ['title'=>'取得方式','field'=>'SPEC']
        ];

        $excel = new ExcelService();
        $excel->exportExcel($data,'cc1',$fieldList);
        $excel->exportExcel($data,'cc2',$fieldList);
        echo date('Y-m-d H:i:s');
    }

    public function importTxt() {
        $file = file_get_contents('D:\wamp64\www\tjugz\public\static\tmp\data.txt');
        $file = fopen($file, 'r');

//存储文件内容

        $file_content=array();
        $i=0;
//输出文本中所有的行，直到文件结束为止。
        while(! feof($file))
        {

            //获取文件内容并存储到数组

            $file_content[$i]= fgets($file);//fgets()函数从文件指针中读取一行
            $i++;
            if ($i>2) {
                break;
            }
        }
        fclose($file);
        $content=array_filter($file_content);
        var_dump($content);
    }

    public function importPage() {
        return $this->fetch('import');
    }

    // 导入ASSET_DICT_TYPE2010数据
    public function importDictType2010() {
        $inputData = input('data');
        $postData = base64_decode($inputData);
        $list = json_decode($postData,true);
        $start = input('start');


        Db::startTrans();
        foreach ($list as $key => $row) {
            $data = [];
            // 字段赋值
            $data['TYPE_ID'] = $row['A'];
            $data['TYPE_NAME'] = $row['B'];
            $data['TYPEUP_ID'] = $row['C'];
            $data['TYPE_USE'] = $row['D'];


            try{
                Db::table('ASSET_DICT_TYPE2010')->insert($data);
            }
            catch (Exception $e){
                Db::rollback();
                return json_encode(['code'=>'0','msg'=>"第".($key + $start)."行数据写入出错",'data'=>$e->getMessage()]);
            }
        }

        Db::commit();
        return json_encode(['code'=>'200','msg'=>'导入成功']);
    }

    // ASSET_DICT_TYPE94TO2010
    public function importDictType94to2010() {
        $inputData = input('data');
        $postData = base64_decode($inputData);
        $list = json_decode($postData,true);
        $start = input('start');


        Db::startTrans();
        foreach ($list as $key => $row) {
            $data = [];
            // 字段赋值
            $data['CCODE94'] = $row['A'];
            $data['CNAME94'] = $row['B'];
            $data['CCODE2010'] = $row['C'];
            $data['CNAME2010'] = $row['D'];


            try{
                Db::table('ASSET_DICT_TYPE94TO2010')->insert($data);
            }
            catch (Exception $e){
                Db::rollback();
                return json_encode(['code'=>'0','msg'=>"第".($key + $start)."行数据写入出错",'data'=>$e->getMessage()]);
            }
        }

        Db::commit();
        return json_encode(['code'=>'200','msg'=>'导入成功']);
    }

    // DICT_COUNTRY
    public function importDictCountry() {
        $inputData = input('data');
        $postData = base64_decode($inputData);
        $list = json_decode($postData,true);
        $start = input('start');


        Db::startTrans();
        foreach ($list as $key => $row) {
            $data = [];
            // 字段赋值
            $data['COUNTRY_ID'] = $row['A'];
            $data['COUNTRY_CNNAME'] = $row['B'];
            $data['COUNTRY_ENNAME'] = $row['C'];
            $data['COUNTRY_SHORT'] = $row['D'];
            $data['ORDERID'] = $row['E'];
            $data['IS_DIS_MARK'] = $row['F'];
            $data['CONTINENTS_ID'] = $row['G'];
            $data['REMARK'] = $row['H'];


            try{
                Db::table('DICT_COUNTRY')->insert($data);
            }
            catch (Exception $e){
                Db::rollback();
                return json_encode(['code'=>'0','msg'=>"第".($key + $start)."行数据写入出错",'data'=>$e->getMessage()]);
            }
        }

        Db::commit();
        return json_encode(['code'=>'200','msg'=>'导入成功']);
    }

    // DICT_SB_DEPARTMENT
    public function importSbDepartment() {
        $inputData = input('data');
        $postData = base64_decode($inputData);
        $list = json_decode($postData,true);
        $start = input('start');


        Db::startTrans();
        foreach ($list as $key => $row) {
            $data = [];
            // 字段赋值
            $data['DEPART_ID'] = $row['A'];
            $data['DEPART'] = $row['B'];
            $data['DEPART_SHORT'] = $row['C'];
            $data['DEPART_BELONG'] = $row['D'];
            $data['EQUIPSTAT_MARK'] = $row['E'];
            $data['DEPART_ID_TEMP'] = $row['F'];
            $data['SHOW_MARK'] = $row['G'];


            try{
                Db::table('DICT_SB_DEPARTMENT')->insert($data);
            }
            catch (Exception $e){
                Db::rollback();
                return json_encode(['code'=>'0','msg'=>"第".($key + $start)."行数据写入出错",'data'=>$e->getMessage()]);
            }
        }

        Db::commit();
        return json_encode(['code'=>'200','msg'=>'导入成功']);
    }


    public function exportExcel(){

//        $sql = "select count(*) from APP_ARCHIVE.ASSET_EQUIP_FACILITY";
//        $count = Db::query($sql);
//        dump($count[0]);
//        return;

        echo "开始时间".date('Y-m-d H:i:s')."<br>";
        $startL = 0;

        $endL = 10000;
        try{
            $listSql = Db::table('APP_ARCHIVE.ASSET_EQUIP_FACILITY')->limit($startL,$endL)->fetchSql()->select();
            $list = Db::query($listSql);
            FacilityTable::exportExcel($list,"设备$startL-$endL");
        }
        catch (Exception $e){
            return $e->getMessage();
        }
        echo "结束时间".date("Y-m-d H:i:s");
    }





}